﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using CommonLib.Model;
using CommonLib.IDAL;

namespace MSSQLDAL
{
    public class BaseCommonDAL : IBaseCommonDAL
    {
        public BaseCommonDAL()
        {

        }

        public string GetDeptIdList(string userid, string menuNo)
        {
            string retStr="";
            string SqlStr = 
            "select * " +
            "         from (  " +
            "         select a.dept_id d1,t.dept_id d2 from sys_dept a,(select b.dept_id  " +
            "         from sys_operator_role a,sys_dept_roles b  " +
            "         where a.master_id=b.master_id and a.user_id='"+userid+"' and b.role_id in  " +
            "					(select role_id from SYS_ROLE_FUNCTION where menu_no='"+menuNo+"') " +
            "         ) t  " +
            "         )p  " +
            "         where Patindex(rtrim(d2)+'%',d1)>0";
            DataTable dt_dept = SqlHelper.ExecuteDataset(Conn.SqlConnStr, CommandType.Text, SqlStr).Tables[0];
            for (int i = 0; i < dt_dept.Rows.Count; i++)
                retStr = retStr+ ","+dt_dept.Rows[i][0].ToString();

            if (retStr.Length > 0)
                return retStr.Remove(0, 1);
            else
                return retStr;

        }

        public string GetDeptIndexList(string userid, string menuNo)
        {
            string retStr = "";
            string SqlStr =
            "select * " +
            "         from (  " +
            "         select a.dept_index,a.dept_id d1,t.dept_id d2 from sys_dept a,(select b.dept_id  " +
            "         from sys_operator_role a,sys_dept_roles b  " +
            "         where a.master_id=b.master_id and a.user_id='" + userid + "' and b.role_id in  " +
            "					(select role_id from SYS_ROLE_FUNCTION where menu_no='" + menuNo + "') " +
            "         ) t  " +
            "         )p  " +
            "         where Patindex(rtrim(d2)+'%',d1)>0";
            DataTable dt_dept = SqlHelper.ExecuteDataset(Conn.SqlConnStr, CommandType.Text, SqlStr).Tables[0];
            for (int i = 0; i < dt_dept.Rows.Count; i++)
                retStr = retStr + "," + dt_dept.Rows[i][0].ToString();

            if (retStr.Length > 0)
                return retStr.Remove(0, 1);
            else
                return retStr;

        }


        public string conVert_Date(string sValue, string sFmt)
        {
            return "'" + sValue + "'";
        }

        //public enum f_date
        //{
        //    yyyymmdd = 112, yymmdd = 112, yy = 112, yymm = 112, yyyy-mm = 117, yy.mm = 107, atAsyn = 6
        //}


        public string Field_Date(string fieldName, string Fmt)
        {
            string formatstr="112";
            if (Fmt.Length > 4)
            {
                 //只判断前5位

                switch (Fmt.Substring(0, 5).ToLower())
                {
                    case "yyyy-": formatstr = "126";
                        break;
                    case "yyyy.": formatstr = "102";
                        break;
                    case "yyyy/": formatstr = "111";
                        break;
                }

            }
            return "Convert(char(" + Fmt.Length + ")," + fieldName + "," + formatstr + ")";
        }

        public int ExecSql(SqlTransaction transaction, string commandText)
        {
            int i = SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, commandText);
            return i;
        }

        public DataSet ExecDataSetSql(SqlTransaction transaction, string commandText)
        {
            DataSet ds = SqlHelper.ExecuteDataset(transaction, CommandType.Text, commandText);
            return ds;
        }
    }
}
